﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace YYS
{
    /// <summary>
    /// 数据库操作类型
    /// </summary>
    public sealed class dbHelper
    {
        /// <summary>
        /// 操作类型
        /// </summary>
        public enum ExecType
        {
            /// <summary>
            /// 默认
            /// </summary>
            Default,
            /// <summary>
            /// 直接执行（当 IsProc = true 时不使用事务）Result = （Bool：标识是否执行成功）
            /// </summary>
            Exec,
            /// <summary>
            /// 第一行第一列 Result = （String or Null）
            /// </summary>
            Scalar,
            /// <summary>
            /// 第一行第一列 （当 IsProc = true 时不使用事务） Result = （String or Null）
            /// </summary>
            ScalarBack,
            /// <summary>
            /// 填充 DataSet Result = （DataSet or Null）
            /// </summary>
            DataSet,
            /// <summary>
            /// 填充 DataTable Result = （DataTable or Null）
            /// </summary>
            DataTable,
            /// <summary>
            /// 读到数据 if(SqlDataReader.Read()) Result = （Bool：标识是否读到数据）
            /// </summary>
            IfRead,
            /// <summary>
            /// 读到数据 while(SqlDataReader.Read()) Result =（Bool：标识是否读到数据）
            /// </summary>
            WhileRead,
            /// <summary>
            /// 自定义操作
            /// </summary>
            Custom = 8
        }

        /// <summary>
        /// 服务器
        /// </summary>
        public enum EnumServer
        {
            /// <summary>
            /// 未指定服务器
            /// </summary>
            _Null,
            /// <summary>
            /// 127.0.0.1
            /// </summary>
            Local,
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        public dbHelper() { }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="__Server">服务器</param>
        /// <param name="__Name">登录名</param>
        /// <param name="__Pwd">登录密码</param>
        public dbHelper(string __Server, string __Name, string __Pwd)
        {
            this.ServerIp = __Server;
            this.UserName = __Name;
            this.UserPwd = __Pwd;
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="serverIp">已知服务器IP</param>
        public dbHelper(string serverIp, bool isSchoolServer = false)
        {
            InitServer(serverIp: serverIp, isSchoolServer: isSchoolServer);
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="server">已知服务器</param>
        public dbHelper(EnumServer server)
        {
            InitServer(server: server);
        }

        void InitServer(string serverIp = null, EnumServer server = EnumServer._Null, bool isSchoolServer = false)
        {
            #region
            switch (server)
            {
                case EnumServer._Null:
                    serverIp = serverIp == null ? "0.0.0.0" : serverIp;
                    break;
                case EnumServer.Local:
                    serverIp = "127.0.0.1";
                    break;
            }
            #endregion
            switch (serverIp)
            {
                case "0.0.0.0":
                    ServerIp = UserName = UserPwd = string.Empty;
                    break;
                case "127.0.0.1":
                    ServerIp = ".";
                    UserName = "sa";
                    UserPwd = "sa";
                    break;
                default:
                    ServerIp = serverIp;
                    UserName = "sa";
                    UserPwd = "sa";
                    break;
            }
        }

        /// <summary>
        /// 服务器
        /// </summary>
        public string ServerIp { get; set; }
        /// <summary>
        /// 数据库
        /// </summary>
        public string DataBase { get; set; }
        /// <summary>
        /// 数据库用户名
        /// </summary>
        private string UserName { get; set; }
        /// <summary>
        /// 数据库密码
        /// </summary>
        private string UserPwd { get; set; }

        private string _CmdText;
        /// <summary>
        /// 要执行的sql语句或存储过程名称
        /// </summary>
        public string CmdText
        {
            get { return _CmdText; }
            set { _CmdText = value; }
        }

        private bool _IsProc = false;
        /// <summary>
        /// 是否执行存储过程 默认false
        /// </summary>
        public bool IsProc
        {
            get { return _IsProc; }
            set { _IsProc = value; }
        }

        bool _NeedOutError = false;
        /// <summary>
        /// 是否自动添加输出参数 @Error VARCHAR(MAX) OUTPUT
        /// </summary>
        public bool NeedOutError
        {
            get { return _NeedOutError; }
            set { _NeedOutError = value; }
        }

        private bool _HasReturnValue = false;
        /// <summary>
        /// 参数是否包含返回值 默认false
        /// </summary>
        public bool HasReturnValue
        {
            get { return _HasReturnValue; }
            set { _HasReturnValue = value; }
        }

        int _ReturnValue = 0;
        /// <summary>
        /// 返回值
        /// </summary>
        public int ReturnValue
        {
            get { return _ReturnValue; }
        }

        private int _ConnectTimeout = 600;
        /// <summary>
        /// SqlConnection超时时间 默认600
        /// </summary>
        public int ConnectTimeout
        {
            get { return _ConnectTimeout; }
            set { _ConnectTimeout = value; }
        }

        private int _CommandTimeout = 600;
        /// <summary>
        /// SqlCommand 超时时间 默认600
        /// </summary>
        public int CommandTimeout
        {
            get { return _CommandTimeout; }
            set { _CommandTimeout = value; }
        }

        private ExecType _execType = ExecType.Default;
        /// <summary>
        /// 要执行操作的类型 ExecType枚举值 默认ExecType.Default 不执行任何操作
        /// </summary>
        public ExecType execType
        {
            get { return _execType; }
            set { _execType = value; }
        }



        private Action<Exception> _FuncError = (ex) => { throw ex; };
        /// <summary>
        /// 当发生异常时执行的操作 默认throw
        /// </summary>
        public Action<Exception> FuncError
        {
            get { return _FuncError; }
            set { _FuncError = value; }
        }

        private Action<SqlDataReader> _FuncRead = null;
        /// <summary>
        /// 当ExecType=IfRead或WhileRead 时，在读取过程中进行的操作
        /// </summary>
        public Action<SqlDataReader> FuncRead
        {
            get { return _FuncRead; }
            set { _FuncRead = value; }
        }

        private Action<SqlCommand> _FuncEnd = null;
        /// <summary>
        /// 当操作完成时 需要继续使用当前命令执行的操作
        /// </summary>
        public Action<SqlCommand> FuncEnd
        {
            get { return _FuncEnd; }
            set { _FuncEnd = value; }
        }

        private Action<SqlCommand> _FuncCustom = null;
        /// <summary>
        /// 当ExecType=Custom时，使用当前命令执行的操作
        /// </summary>
        public Action<SqlCommand> FuncCustom
        {
            get { return _FuncCustom; }
            set { _FuncCustom = value; }
        }

        private List<SqlParameter> _Parameters = new List<SqlParameter>();
        /// <summary>
        /// 参数
        /// </summary>
        public List<SqlParameter> Parameters
        {
            get { return _Parameters; }
            set { _Parameters = value; }
        }

        /// <summary>
        /// 获取数据库连接字符串
        /// </summary>
        public string ConnectString
        {
            get
            {
                //MultipleActiveResultSets=true;
                return "packet size=4096;data source=" + ServerIp + ";persist security info=True;initial catalog=" + DataBase + ";user id= " + UserName + ";password=" + UserPwd + ";Pooling=true;Max Pool Size=512;Min Pool Size=1;Connection Lifetime= 180;MultipleActiveResultSets=true;Connect Timeout=" + _ConnectTimeout + "";
            }
        }

        private dynamic _Result = null;
        /// <summary>
        /// 执行的结果
        /// </summary>
        public dynamic Result
        {
            get { return _Result; }
        }

        private Exception _Ex = null;
        /// <summary>
        /// 详细异常
        /// </summary>
        public Exception Ex
        {
            get { return _Ex; }
        }

        private string _Error = null;
        /// <summary>
        /// 执行发生的错误（简略信息）
        /// </summary>
        public string Error
        {
            get { return _Error != null && _Error.Trim().Length > 0 ? _Error : null; }
        }
        /// <summary>
        /// 是否包含执行错误
        /// </summary>
        public bool HasError
        {
            get
            {
                return this.Error != null && this.Error.Trim().Length > 0;
            }
        }
        /// <summary>
        /// 是否包含系统错误
        /// </summary>
        public bool HasSystemError
        {
            get
            {
                return this.HasError && this.Error.IndexOf("SystemError", StringComparison.OrdinalIgnoreCase) > -1;
            }
        }

        /// <summary>
        /// 设置自定义错误信息
        /// </summary>
        /// <param name="error"></param>
        public void SetError(object error)
        {
            string err = error == null ? string.Empty : error.ToString().Trim();
            this._Error = err.Length == 0 ? null : err;
        }

        /// <summary>
        /// 获取当前对象详细信息
        /// </summary>
        /// <returns></returns>
        public object getDetail()
        {
            Dictionary<string, object> dic = new Dictionary<string, object>();
            return new
            {
                ServerIp = ServerIp,
                DataBase = DataBase,
                UserName = UserName,
                UserPwd = "**",
                execType = execType.ToString(),
                IsProc = IsProc,
                HasReturnValue = HasReturnValue,
                CmdText = CmdText,
                Parameters = _Parameters.Select(v => new
                {
                    ParameterName = v.ParameterName,
                    SqlDbType = v.SqlDbType.ToString(),
                    Size = v.Size,
                    Direction = v.Direction.ToString(),
                    Value = v.Value
                }),
                Result = Result,
                Error = Error,
                Ex = dic
            };
        }

        private Dictionary<string, object> _OutParameters = new Dictionary<string, object>();
        /// <summary>
        /// 输出参数 不带@
        /// </summary>
        public Dictionary<string, object> OutParameters
        {
            get { return _OutParameters; }
        }

        /// <summary>
        /// 向Parameters中添加一个参数
        /// </summary>
        /// <param name="parameterName">参数名称</param>
        /// <param name="sqlDbType">数据库类型</param>
        /// <param name="Value">值</param>
        /// <param name="Size">长度</param>
        /// <param name="Precision">总位数</param>
        /// <param name="Scale">小数位数</param>
        /// <param name="parameterDirection">参数方向</param>
        public void AddParameter(string parameterName, SqlDbType sqlDbType, object Value, int Size = int.MinValue, byte Precision = byte.MaxValue, byte Scale = byte.MaxValue, ParameterDirection parameterDirection = ParameterDirection.Input)
        {
            if (!parameterName.StartsWith("@"))
            {
                parameterName = "@" + parameterName;
            }
            SqlParameter sqlParameter = new SqlParameter(parameterName, sqlDbType);
            sqlParameter.Value = Value == null ? DBNull.Value : Value;
            if (Size != int.MinValue) sqlParameter.Size = Size;
            if (Precision != byte.MaxValue) sqlParameter.Precision = Precision;
            if (Scale != byte.MaxValue) sqlParameter.Scale = Scale;
            if (parameterDirection != ParameterDirection.Input) sqlParameter.Direction = parameterDirection;
            _Parameters.Add(sqlParameter);
        }

        /// <summary>
        /// 主执行方法
        /// </summary>
        public void Exec()
        {
            if (_execType == ExecType.Default) return;//ExecType.Default没有任何操作

            SqlConnection con = null;
            try
            {
                using (con = new SqlConnection(this.ConnectString))
                {
                    con.Open();
                    using (SqlCommand cmd = con.CreateCommand())
                    {
                        cmd.Connection = con;
                        cmd.CommandTimeout = _CommandTimeout;//SqlCommand 超时时间 默认600
                        if (_execType != ExecType.Custom)
                        {
                            cmd.CommandType = _IsProc ? CommandType.StoredProcedure : CommandType.Text;//判断是存储过程还是SQL语句
                            cmd.CommandText = _CmdText;
                            #region 参数
                            cmd.Parameters.Clear();
                            if (_Parameters != null)
                            {
                                foreach (SqlParameter Parameter in _Parameters)
                                {
                                    if (Parameter.Direction == ParameterDirection.Input || Parameter.Direction == ParameterDirection.InputOutput)
                                    {
                                        Parameter.Value = Parameter.Value == null ? DBNull.Value : Parameter.Value;
                                    }
                                    cmd.Parameters.Add(Parameter);
                                }
                            }
                            if (_HasReturnValue)
                            {
                                cmd.Parameters.Add(new SqlParameter("@ReturnValue", SqlDbType.Int)
                                {
                                    Direction = ParameterDirection.ReturnValue
                                });
                            }
                            if (_NeedOutError)
                            {
                                cmd.Parameters.Add(new SqlParameter("@Error", SqlDbType.VarChar, -1)
                                {
                                    Direction = ParameterDirection.Output
                                });
                            }
                            #endregion
                            #region 操作
                            switch (_execType)
                            {
                                case ExecType.Exec:
                                    _Result = false;
                                    if (!_IsProc)
                                    {
                                        using (SqlTransaction tran = con.BeginTransaction(Guid.NewGuid().ToString("N")))
                                        {
                                            try
                                            {
                                                cmd.Transaction = tran;
                                                cmd.ExecuteNonQuery();
                                                tran.Commit();
                                                _Result = true;
                                            }
                                            catch
                                            {
                                                if (tran.Connection != null) tran.Rollback();
                                                throw;
                                            }
                                        };
                                    }
                                    else
                                    {
                                        try
                                        {
                                            cmd.ExecuteNonQuery();
                                            _Result = true;
                                        }
                                        catch { throw; }
                                    }
                                    break;
                                case ExecType.Scalar:
                                    var ScalarResult = cmd.ExecuteScalar();
                                    _Result = ScalarResult == null ? null : ScalarResult.ToString().Trim();
                                    break;
                                case ExecType.ScalarBack:
                                    if (!_IsProc)
                                    {
                                        using (SqlTransaction tran = con.BeginTransaction(Guid.NewGuid().ToString("N")))
                                        {
                                            try
                                            {
                                                cmd.Transaction = tran;
                                                var ScalarBackResult = cmd.ExecuteScalar();
                                                _Result = ScalarBackResult == null ? null : ScalarBackResult.ToString().Trim();
                                                tran.Commit();
                                            }
                                            catch
                                            {
                                                if (tran.Connection != null) tran.Rollback();
                                                throw;
                                            }
                                        };
                                    }
                                    else
                                    {
                                        try
                                        {
                                            var ScalarBackResult = cmd.ExecuteScalar();
                                            _Result = ScalarBackResult == null ? null : ScalarBackResult.ToString().Trim();
                                        }
                                        catch { throw; }
                                    }
                                    break;
                                case ExecType.DataSet:
                                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                                    {
                                        sda.SelectCommand.CommandTimeout = _CommandTimeout;
                                        DataSet ds = new DataSet();
                                        sda.Fill(ds);
                                        _Result = ds;
                                    }
                                    break;
                                case ExecType.DataTable:
                                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                                    {
                                        sda.SelectCommand.CommandTimeout = _CommandTimeout;
                                        DataSet ds = new DataSet();
                                        sda.Fill(ds);
                                        if (ds.Tables.Count > 0)
                                        {
                                            _Result = ds.Tables[0];
                                        }
                                    }
                                    break;
                                case ExecType.IfRead:
                                    _Result = false;
                                    using (SqlDataReader sdr = cmd.ExecuteReader())
                                    {
                                        if (sdr.Read())
                                        {
                                            _Result = true;
                                            if (_FuncRead != null) _FuncRead(sdr);
                                        }
                                        sdr.Close();
                                    }
                                    break;
                                case ExecType.WhileRead:
                                    _Result = false;
                                    using (SqlDataReader sdr = cmd.ExecuteReader())
                                    {
                                        while (sdr.Read())
                                        {
                                            _Result = true;
                                            if (_FuncRead != null) _FuncRead(sdr);
                                        }
                                        sdr.Close();
                                    }
                                    break;
                            }
                            #endregion
                            #region 操作完成 给输出参数或返回参数赋值
                            if (cmd.Parameters != null && cmd.Parameters.Count > 0)
                            {
                                foreach (SqlParameter Parameter in cmd.Parameters)
                                {
                                    _OutParameters.Add(Parameter.ParameterName.Replace("@", ""), cmd.Parameters[Parameter.ParameterName].Value);
                                }
                                if (_NeedOutError)
                                {
                                    var er = _OutParameters["Error"] == null ? "" : _OutParameters["Error"].ToString();
                                    if (er.Trim().Length > 0) _Error = er;                                    
                                }
                                if (_HasReturnValue)
                                {
                                    var rv = _OutParameters["ReturnValue"] == null ? "" : _OutParameters["ReturnValue"].ToString();
                                    int rvi;
                                    if (int.TryParse(rv, out rvi)) _ReturnValue = rvi;
                                }
                            }
                            if (_execType == ExecType.DataTable)
                            {
                                if (_Error == null && _Result == null)
                                {
                                    throw new Exception("No Data Be Filled");
                                }
                            }
                            if (_FuncEnd != null) _FuncEnd(cmd);
                            #endregion
                        }
                        else
                        {
                            if (_FuncCustom != null) _FuncCustom(cmd);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _Ex = ex;
                _Error = "[SystemError]" + ex.Message;
                if (_FuncError != null)
                {
                    _FuncError(ex);
                }
            }
            finally
            {
                if (con != null)
                {
                    try
                    {
                        con.Close();
                        con.Dispose();
                        con = null;
                    }
                    catch { }
                }
            }
        }

        /// <summary>
        /// SqlBulkCopy DataTable批量导入数据库
        /// </summary>
        /// <param name="TableName">服务器目标表名</param>
        /// <param name="Mappings">数据源与目标表列映射</param>
        /// <param name="copyTable">要导入的DataTable</param>
        public void BulkCopy(string TableName, List<SqlBulkCopyColumnMapping> Mappings, DataTable copyTable)
        {
            _Result = false;
            try
            {
                using (SqlBulkCopy copy = new SqlBulkCopy(this.ConnectString, SqlBulkCopyOptions.UseInternalTransaction | SqlBulkCopyOptions.FireTriggers))
                {
                    copy.BulkCopyTimeout = _CommandTimeout;
                    copy.DestinationTableName = TableName;
                    copy.ColumnMappings.Clear();
                    if (Mappings != null && Mappings.Count > 0)
                    {
                        foreach (var o in Mappings)
                        {
                            copy.ColumnMappings.Add(o);
                        }
                    }
                    copy.WriteToServer(copyTable);
                    _Result = true;
                }
            }
            catch (Exception ex)
            {
                _Ex = ex;
                _Error = "[SystemError]" + ex.Message;
                if (_FuncError != null)
                {
                    _FuncError(ex);
                }
            }
        }

        /// <summary>
        /// 清除当前设置为默认 
        /// 不清除ServerIp ， DataBase ， UserName ， UserPwd
        /// </summary>
        public void Clear()
        {
            CmdText = string.Empty;
            IsProc = HasReturnValue = false;
            ConnectTimeout = CommandTimeout = 600;
            execType = ExecType.Default;
            _Result = null;
            _Error = null;
            Parameters.Clear();
            _OutParameters.Clear();
            FuncRead = null;
            FuncEnd = null;
            FuncCustom = null;
            FuncError = (ex) => { throw ex; };
        }
    }
}
